IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SrvrInstncDatabase]') AND type in (N'U'))
BEGIN
     DROP TABLE [dbo].[SrvrInstncDatabase]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SrvrInstncDatabase](
     [SrvrInstncDatabaseId] [int] IDENTITY(1,1) NOT NULL,
     [SrvrInstncId]         [int]               NOT NULL,
     [PackageExecutionId]   [bigint]            NOT NULL,
     [DatabaseName]         [varchar](128)      NOT NULL,
     [EnabledFlag]          [tinyint]           NOT NULL CONSTRAINT [DF_SrvrInstncDatabase_EnabledFlag] DEFAULT ((0)),
     [LastModDt]            [datetime]          NOT NULL CONSTRAINT [DF_SrvrInstncDatabase_LastModDt] DEFAULT (getdate()),
     CONSTRAINT [PK_SrvrInstncDatabase] PRIMARY KEY CLUSTERED ( [SrvrInstncDatabaseId] ASC )
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_SrvrInstncDatabase_ServerKey] ON [dbo].[SrvrInstncDatabase] 
     ( [SrvrInstncId] ASC, [DatabaseName] ASC ) ON [Data_Index]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Database on a Database Server Instance.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SrvrInstncDatabase', @level2type=N'COLUMN',@level2name=N'SrvrInstncDatabaseId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Database Server Instance.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SrvrInstncDatabase', @level2type=N'COLUMN',@level2name=N'SrvrInstncId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Execution Id of the package that populated or updated this row (usually by SSIS).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SrvrInstncDatabase', @level2type=N'COLUMN',@level2name=N'PackageExecutionId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the database.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SrvrInstncDatabase', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag indicating whether this database has been deleted,' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SrvrInstncDatabase', @level2type=N'COLUMN',@level2name=N'EnabledFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server instance databases.  Contains effective dated list of all databases and database history for a server instance.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SrvrInstncDatabase'
GO
